package com.hh.bss.util;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelHelper {
	public static HSSFWorkbook createExcelWorkbook(String sheetname, List datas, Map title) throws IOException {
		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			HSSFSheet sheet = wb.createSheet(sheetname);
			HSSFRow row = sheet.createRow((short) 0);
			row.setHeight((short) 300);
			// 设置表头
			HSSFFont titleFont = wb.createFont();
			titleFont.setFontHeightInPoints((short) 10);
			titleFont.setFontName("宋体");
			titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置粗体

			HSSFCellStyle titleStyle = wb.createCellStyle();
			titleStyle.setFont(titleFont);
			titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

			int lineCount = 0;
			row = sheet.createRow(lineCount);
			lineCount++;
			HSSFCell cell = null;
			int iCount = 0;
			for (Iterator it = title.keySet().iterator(); it.hasNext();) {
				String key = (String) it.next();

				cell = row.createCell(iCount);
				iCount++;
				cell.setCellStyle(titleStyle);
				cell.setCellValue(new HSSFRichTextString((String) title.get(key)));
			}
			// 设置表内容
			HSSFFont valueFont = wb.createFont();
			valueFont.setFontHeightInPoints((short) 10);
			valueFont.setFontName("宋体");
			valueFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);

			HSSFCellStyle valueStyle = wb.createCellStyle();
			valueStyle.setFont(valueFont);
			valueStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

			// lineCount++;
			// row = sheet.createRow(lineCount);
			// 处理一行
			HSSFCellStyle valueStyleNumber = wb.createCellStyle();
			HSSFDataFormat formatNumber = wb.createDataFormat();
			valueStyleNumber.setDataFormat(formatNumber.getFormat("General"));

			HSSFCellStyle valueStylePercent = wb.createCellStyle();
			HSSFDataFormat formatPercent = wb.createDataFormat();
			valueStylePercent.setDataFormat(formatPercent.getFormat("0.00%"));

			for (int i = 0; i < datas.size(); i++) {
				Map line = (Map) datas.get(i);
				iCount = 0;

				row = sheet.createRow(lineCount);
				lineCount++;
				for (Iterator it = title.keySet().iterator(); it.hasNext();) {
					String key = (String) it.next();

					cell = row.createCell(iCount);
					iCount++;

					cell.setCellStyle(valueStyleNumber);

					// cell.setCellValue(new
					// HSSFRichTextString(ObjectUtils.toString(line.get(key))));

					if (ExcelHelper.isDouble(line.get(key))) {

						cell.setCellValue(Double.parseDouble(line.get(key).toString()));
					} else if (isPercent(line.get(key))) {
						cell.setCellStyle(valueStylePercent);
						cell.setCellValue(getPercentDouble(line.get(key).toString()));
					} else
						cell.setCellValue(new HSSFRichTextString(ObjectUtils.toString(line.get(key))));

					try {
						cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
					} catch (Exception ex1) {
					}
				}
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return wb;
	}

	public static void setExcelSheetData(Sheet sheet, List datas, List<String> title, List<String> key) {
		try {

			Row row = sheet.createRow(0);
			Cell cell = null;
			// 表头
			for (int i = 0; i < title.size(); i++) {
				cell = row.createCell(i);
				cell.setCellValue(title.get(i));
			}

			// 开始创建数据行
			for (int i = 0; i < datas.size(); i++) {
				row = sheet.createRow(i + 1);
				HashMap values = (HashMap) datas.get(i);

				for (int j = 0; j < key.size(); j++) {
					cell = row.createCell(j);
					cell.setCellValue(ObjectUtils.toString(values.get(key.get(j))));
				}
			}
			/*
			 * int iCount = 0; for (Iterator it = title.keySet().iterator();
			 * it.hasNext();) { String key = (String) it.next();
			 * 
			 * cell = row.createCell(iCount); iCount++;
			 * cell.setCellValue(((String) title.get(key))); }
			 * 
			 * for (int i = 0; i < datas.size(); i++) { Map line = (Map)
			 * datas.get(i); iCount = 0;
			 * 
			 * row = sheet.createRow(lineCount); lineCount++; for (Iterator it =
			 * title.keySet().iterator(); it.hasNext();) { String key = (String)
			 * it.next();
			 * 
			 * cell = row.createCell(iCount); iCount++;
			 * cell.setCellValue((ObjectUtils.toString(line.get(key))));
			 * 
			 * } }
			 */

		} catch (Exception ex) {
			ex.printStackTrace();
		}

	}

	public static HSSFWorkbook createReportExcel(String sheetname, List<HashMap<String, String>> datas, List<String> title, List<String> key) throws IOException {
		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			// 创建一个excel sheet
			HSSFSheet sheet = wb.createSheet(sheetname);
			// 表条行
			HSSFRow row = sheet.createRow(0);

			HSSFCell cell = null;
			// 行高
			row.setHeight((short) 300);
			// 设置字体
			HSSFFont titleFont = wb.createFont();
			titleFont.setFontHeightInPoints((short) 10);
			// 设置字体
			titleFont.setFontName("宋体");
			// 粗体
			titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置粗体

			HSSFCellStyle titleStyle = wb.createCellStyle();
			titleStyle.setFont(titleFont);
			// 对齐方式
			titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

			// 表头
			for (int i = 0; i < title.size(); i++) {
				cell = row.createCell(i);
				cell.setCellValue(title.get(i));
			}

			// 开始创建数据行
			for (int i = 0; i < datas.size(); i++) {
				row = sheet.createRow(i + 1);
				HashMap<String, String> values = datas.get(i);

				for (int j = 0; j < key.size(); j++) {
					cell = row.createCell(j);
					cell.setCellValue(values.get(key.get(i)));
				}
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return wb;
	}

	/**
	 * 
	 * @param fileName
	 *            文件名
	 * @param map
	 *            参数->列 map
	 * @param obj
	 *            实体对象
	 */
	public static List readExcelSaveToCompany(String fileName, Map map, Object obj) {
		return new ArrayList();
	}

	/**
	 * 
	 * @param inputStream
	 *            文件流
	 * @param map
	 *            参数->列 map
	 * @param obj
	 *            实体对象
	 */
	public static List readExcelSaveToCompany(InputStream inputStream, Map map, Object obj) {
		return new ArrayList();

	}

	public static boolean isDouble(Object o) {

		// 如果是数值型，则返回Double
		try {
			Double.parseDouble(o.toString());
			return true;
		} catch (Exception ex) {
		}
		return false;
	}

	public static boolean isPercent(Object o) {

		// 如果是数值型，则返回Double
		try {
			String a = o.toString();
			if (a.indexOf("%") >= 0) {
				a = o.toString().replaceAll("\\%", "");
				Double.parseDouble(a);
				return true;
			}
		} catch (Exception ex) {

		}
		return false;
	}

	public static Double getPercentDouble(Object o) {
		try {
			String a = o.toString();
			if (a.indexOf("%") >= 0) {
				a = o.toString().replaceAll("\\%", "");
				Double d = Double.parseDouble(a);
				return Double.valueOf(d.doubleValue() / 100);
			}
		} catch (Exception ex) {
		}
		return null;
	}

	/**
	 * 把单元格内容转换成String
	 * 
	 * @param cell
	 * @return
	 */
	public static String getStringValue(HSSFCell cell) {
		String cellSting = null;
		if (cell != null) {
			switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_STRING: // 字符串
				cellSting = cell.getStringCellValue();
				break;
			case HSSFCell.CELL_TYPE_NUMERIC: // 数字
				DecimalFormat df = new DecimalFormat("#");
				cellSting = df.format(cell.getNumericCellValue());
				break;
			default:
				break;
			}
		}
		if (cellSting != null && !"".equals(cellSting.trim())) {
			return cellSting;
		}
		return null;
	}

	/**
	 * 生产SL报告
	 * 
	 * @return
	 */
	public static Workbook searchReport() {
		Workbook wb = new XSSFWorkbook(); // 创建一个07以上的Excel
		Sheet sheet1 = wb.createSheet("Search Report");
		Sheet sheet2 = wb.createSheet("顾问面试人选明细");
		Sheet sheet3 = wb.createSheet("联系人选明细");
		return wb;

	}

}
